package de.fhb.easymealtime.data;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import de.fhb.easymealtime.repository.rezeptVO;
import de.fhb.easymealtime.repository.zutatVO;

public class RezeptDao extends JdbcDAOBase implements IRezeptDAO {

	
	public RezeptDao(){
		super();
	}
	
	@Override
	public rezeptVO getRezeptByID(int ID) {
		rezeptVO vo;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		String sqlStr = "Select * From Rezept WHERE ID like ?";
		try{
			
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			ps.setInt(1, ID);
			rs = ps.executeQuery();
			vo = (rezeptVO)transformSetToVO(rs);
			vo.setZutaten(getZutaten(vo.getID()));
		}catch(SQLException sqle){
			throw new DataAccessException("Error executing");
		} finally {
			try{
				if(ps != null)
					ps.close();
				if(con != null)
					con.close();
			}catch(SQLException sqlee){
				throw new DataAccessException("Can't close connection",sqlee);
			}
		}
		return vo;
	}

	@Override
	public rezeptVO getRezeptByName(String Name) {
		rezeptVO vo;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		String sqlStr = "Select * From Rezept WHERE Name like ?";
		try{
			
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			ps.setString(1, Name);
			rs = ps.executeQuery();
			vo = (rezeptVO)transformSetToVO(rs);
			vo.setZutaten(getZutaten(vo.getID()));
		}catch(SQLException sqle){
			throw new DataAccessException("Error executing");
		} finally {
			try{
				if(ps != null)
					ps.close();
				if(con != null)
					con.close();
			}catch(SQLException sqlee){
				throw new DataAccessException("Can't close connection",sqlee);
			}
		}
		return vo;
	}

	@Override
	public List<rezeptVO> getAllRezepte() {
		List<rezeptVO> liste = null;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "Select * From Rezept";
		
		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			rs = ps.executeQuery();
			liste = transformSet(rs);
			for(rezeptVO vo : liste)
				vo.setZutaten(getZutaten(vo.getID()));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
		try{
			if(ps != null)
				ps.close();
			if(con != null)
				con.close();
		}catch(SQLException sqlee){
			throw new DataAccessException("Can't close connection",sqlee);
		}
		
		}
		
		
		return liste;
	}

	@Override
	protected Object map2VO(ResultSet rs) throws SQLException {
		rezeptVO vo = new rezeptVO();
		vo.setID(rs.getInt("ID"));
		vo.setName(rs.getString("Name"));
		vo.setRezept(rs.getString("Rezept"));
		return vo;
	}

	@Override
	public void create(rezeptVO rezept) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int key = -1;
		
		String sqlStr = "INSERT INTO Rezept (Name,Rezept) VALUES (?, ?)";
		
		
		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr,PreparedStatement.RETURN_GENERATED_KEYS);
			
			int i=1;
			ps.setString(i++, rezept.getName());
			ps.setString(i++, rezept.getRezept());
			ps.executeUpdate();
			rs = ps.getGeneratedKeys();
			if(rs.next())
				key =rs.getInt(1);
			else
				throw new DataAccessException("Error executing");
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if(ps != null)
					ps.close();
				if(con != null)
					ps.close();
			}catch(SQLException sqlee){
				throw new DataAccessException("cant close",sqlee);
			}
		}
		
	}
	
	
	private List<zutatVO> getZutaten(int RezeptID){
		List<zutatVO>liste = new ArrayList<zutatVO>();
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		String sqlStr = "Select * From hat_Zutaten WHERE RezeptID like ?";
		
		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			ps.setInt(1, RezeptID);
			rs = ps.executeQuery();
			while(rs.next())
			{
				int ZutatenID = rs.getInt("ZutatenID");
				ZutatDao dao = new ZutatDao();
				liste.add(dao.getZutatByID(ZutatenID));
			}
			
		} catch (SQLException e) {
			throw new DataAccessException("Can't get Ingrediants",e);
		}finally{
			try {
				if(ps != null)
					ps.close();
				if(con != null)
					con.close();
				
			}catch(SQLException sqlee){
				throw new DataAccessException("cant close",sqlee);
				
			}
			
			
		}
		
		return liste;
		
	}
	

}
